﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Mysqlx.Crud;
using TimerTask.Common.Utils;
using TimerTask.Model.Entity;
using TimerTask.Model.Vms;

namespace TimerTask.Dal
{
    public class JobInfoDal : BaseDal
    {

        /// <summary>
        /// 添加任务
        /// author:肖阳
        /// 2016-10-22 13:28:02
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddJobInfo(JobInfo model)
        {
            string sql =
                @"INSERT INTO jobinfo (JobName, RequestUrl, GroupId, ContactEmail, ContactTel, CreateTime, UpdateTime, Cron, LastFireTime, ResAssert, JobStatus)
VALUES (?JobName, ?RequestUrl, ?GroupId, ?ContactEmail, ?ContactTel, ?CreateTime, ?UpdateTime, ?Cron, ?LastFireTime, ?ResAssert, ?JobStatus);";
            try
            {
                return DbConnection.Execute(sql, model);
            }
            catch (Exception exception)
            {
                LogUtil.Error(exception, "添加任务失败model:{0}", model.ToJson());
                return 0;
            }
        }

        /// <summary>
        /// 获取分页
        /// author:肖阳
        /// 2016-10-22 13:49:46
        /// </summary>
        /// <param name="index">当前页</param>
        /// <param name="groupId">组Id</param>
        /// <returns></returns>
        public List<JobInfo> GetJobInfo(int index, int groupId)
        {
            string sql = @"SELECT * FROM  jobinfo ";
            List<string> conditions = new List<string>();
            string item = string.Empty;
            if (index > 0)
            {
                item = "limit " + (index - 1) + ", " + AppConfig.Instance.PageSize;
                conditions.Add(item);
            }
            if (groupId > 0)
            {
                item = " GroupId = ?groupId ";
                conditions.Add(item);
            }
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }
            return DbConnection.Query<JobInfo>(sql, new { groupId, index }).ToList();
        }

        /// <summary>
        /// 查询单个任务
        /// author:肖阳
        /// date:2016-10-22 13:20:34
        /// </summary>
        /// <param name="jobId"></param>
        /// <returns></returns>
        public JobInfo Query(int jobId)
        {
            string sql = "SELECT * FROM JOBINFO WHERE ID = ?jobId";
            return DbConnection.Query<JobInfo>(sql, new { jobId }).SingleOrDefault();
        }

        /// <summary>
        /// 分页获取JobInfoVms
        /// author:肖阳
        /// 2016-10-22 14:45:05
        /// </summary>
        /// <param name="index"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<JobInfoVm> GetJobInfoVms(int index, int groupId)
        {
            string sql = @"SELECT io.*,g.GroupName FROM jobinfo  io LEFT JOIN  groupinfo g on  g.Id = io.GroupId  ";
            List<string> conditions = new List<string>();
            string limit = string.Empty;
            if (index > 0)
            {
                limit = "limit " + (index - 1) * AppConfig.Instance.PageSize + ", " + AppConfig.Instance.PageSize;
            }
            if (groupId > 0)
            {
                conditions.Add(" GroupId = ?groupId ");
            }
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }
            sql += limit;
            return DbConnection.Query<JobInfoVm>(sql, new { groupId, index }).ToList();
        }


        /// <summary>
        /// 获取分页
        /// author:肖阳
        /// 2016-10-22 13:49:46
        /// </summary>
        /// <param name="groupId">组Id</param>
        /// <returns></returns>
        public int GetJobInfoCount(int groupId)
        {
            string sql = @"SELECT COUNT(1) FROM  jobinfo ";
            if (groupId > 0)
            {
                sql += "WHERE  GroupId = ?groupId ";
            }
            return DbConnection.ExecuteScalar<int>(sql, new { groupId });
        }


        /// <summary>
        /// 获取所有任务
        /// author:肖阳
        /// 2016-10-22 13:49:46
        /// </summary>
        /// <returns></returns>
        public List<JobInfo> GetAllJobInfo()
        {
            string sql = @"SELECT * FROM  jobinfo ";
            return DbConnection.Query<JobInfo>(sql).ToList();
        }

        /// <summary>
        /// 更新任务触发时间
        /// author:肖阳
        /// date:2016-10-22 13:20:34
        /// </summary>
        /// <param name="id"></param>
        public void UpdateFireTime(int id)
        {
            string sql = @"UPDATE jobinfo SET LastFireTime = ?LastFireTime WHERE Id = ?Id;";
            try
            {
                DbConnection.Execute(sql, new { Id = id, LastFireTime = DateTime.Now });
            }
            catch (Exception exception)
            {
                LogUtil.Error(exception, "更新任务触发事件失败JobInfo:{0}", id.ToJson());
            }
        }


        /// <summary>
        /// 更新任务状态
        /// author:肖阳
        /// date:2016-10-22 13:20:34
        /// </summary>
        /// <param name="jobInfo"></param>
        /// <returns></returns>
        public int UpdateStatus(JobInfo jobInfo)
        {
            string sql = "UPDATE jobinfo SET JobStatus = ?JobStatus,UpdateTime = ?UpdateTime WHERE Id=?Id;";

            try
            {
                return DbConnection.Execute(sql, jobInfo);
            }
            catch (Exception exception)
            {
                LogUtil.Error(exception, "更新任务状态失败JobInfo:{0}", jobInfo.ToJson());
            }
            return 0;
        }

        /// <summary>
        /// 统计数量
        /// author:肖阳
        /// date:2016-10-22 13:20:34
        /// </summary>
        /// <returns></returns>
        public Tuple<string, string, string> GetJobCounts()
        {
            string sql = @"set @n1:= (
    SELECT count(1)
    FROM jobinfo
    WHERE JobStatus IN (1, 2));
set @n2:= (SELECT count(1)
   FROM jobinfo
   WHERE JobStatus = 1);

set @n3 =  (SELECT count(1)
   FROM alarmlog
   WHERE Result = 2) ;

SELECT
 cast(@n1 as SIGNED) ,
  cast(@n2 as SIGNED),
  cast(@n3 as SIGNED);";

            return
                DbConnection.Query<long, long, long, Tuple<string, string, string>>(sql, (x, y, z) => Tuple.Create(x.ToString(), y.ToString(), z.ToString()), splitOn: "*")
                    .FirstOrDefault();
        }
    }
}
